<?php

class DataController {
    
    private $con;
    
    public function __construct( ) {
    }    
    
    public function connect() {
        $this->con = mysql_connect("localhost","atc","atc2627");
        if (!$this->con) {
            die('Could not connect: ' . mysql_error());
        }
        mysql_select_db("epart", $this->con);
        mysql_set_charset('utf8', $this->con);
    }
    
    public function disconnect() {
        mysql_close($this->con);
    }
        
    public function getTagNames($name) {        
        $tags = array();
        
        $name = mysql_real_escape_string($name);
        
        $result = mysql_query("SELECT Name FROM Tags WHERE Name LIKE '%$name%';");
           
        if ($result  && mysql_num_rows($result)) {           
            while ($row = mysql_fetch_assoc($result)) {
                $tags[] = $row['Name'];
            }
        }        
        return $tags;
    }  
    
    public function saveAction($action) {
        $type = $action->getType();
        $name = mysql_real_escape_string($action->getName());
        $description = mysql_real_escape_string($action->getDescription());
        $username = mysql_real_escape_string($action->getUser());
        $seriesId = $action->getSeriesId();
        $query = "INSERT INTO Actions VALUES(NULL, '$username', '$type',
                '$name', '$description', NOW(), NULL, 0, 0, $seriesId)";
        if (!mysql_query($query)) {
            return -1;        
        }     
        $actionId = mysql_insert_id();        
        
        if ($type == "petition") {
            $query = "INSERT INTO Petitions VALUES($actionId, '{$action->getStartDate()}',
                    '{$action->getStopDate()}', {$action->getRequiresLogIn()}, 0)";
            if (!mysql_query($query)) {
                mysql_query("DELETE FROM actions WHERE ActionId=$actionId");
                return -1;
            }
        }
        elseif ($type== "survey") {
            $query = "INSERT INTO Surveys VALUES($actionId, '{$action->getStartDate()}',
                    '{$action->getStopDate()}', {$action->getRequiresLogIn()})";
            if (!mysql_query($query)) {
                mysql_query("DELETE FROM actions WHERE ActionId=$actionId");                
                return -1;
            }            
        }
        elseif ($type == "poll") {
            
        }
        elseif ($type == "deliberation") {
            
        }

        $this->saveTags($action->getTags(), $username, $actionId);
        
        return $actionId;        
    }    
    
    private function saveTags($tags, $user, $actionId) {
        $tags = trim($tags);
        $tagArray = explode(" ", $tags);
        $tagArray = array_unique($tagArray);
        foreach($tagArray as $tag) {
            $tagId = $this->getTagByName($tag);
            if ($tagId <= 0) {
                $tagId = $this->createTag($tag, $user);                
            }
            $this->addActionTag($actionId, $tagId);
        }
    }
    
    private function getTagByName($tag) {
        $tag = mysql_real_escape_string($tag);
        
        $result = mysql_query("SELECT TagId FROM Tags WHERE Name='$tag';");
           
        if ($result  && mysql_num_rows($result)) {           
            $row = mysql_fetch_assoc($result);
            return $row["TagId"];
        }        
        return 0;
    }
    
    private function createTag($tag, $user) {
        $tag = mysql_real_escape_string($tag);
        mysql_query("INSERT INTO Tags VALUES(NULL, '$user', '$tag')");
        return mysql_insert_id();
    }    
    
    private function addActionTag($actionId, $tagId) {
        mysql_query("INSERT INTO ActionTags VALUES($actionId, $tagId)");
    }
    
    /**
     * $arguments Can be empty or include one or more of: type, creator, from, to, tags,
     *             page, pageSize
     *             Returns an ActionsPage object
     */
    public function getActions($arguments) {
        $query = "SELECT Actions.ActionId, Actions.User AS Username, Actions.Type, Actions.Name, Actions.Description,
                        Actions.Created, Actions.Modified, Actions.Popularity, Actions.Visits, Actions.SeriesId,
                        GROUP_CONCAT(Tags.Name SEPARATOR ' ') AS Tags FROM Actions
                        INNER JOIN ActionTags ON ActionTags.ActionId = Actions.ActionId
                        INNER JOIN Tags ON ActionTags.TagId = Tags.TagId";
        $countQuery = "SELECT COUNT(DISTINCT Actions.ActionId) as Count FROM Actions
                        INNER JOIN ActionTags ON ActionTags.ActionId = Actions.ActionId
                        INNER JOIN Tags ON ActionTags.TagId = Tags.TagId ";
        $where = "WHERE 1=1 ";

        extract($arguments);
        
        if (isset($type)) {
            $type = mysql_real_escape_string($type );
            $where = "$where AND Actions.Type='$type' ";
        }
        
        if (isset($from)) {
            $dateFrom = date("Y-m-d", Utilities::getTimestamp($from));            
            $where = "$where AND IF(Actions.Modified IS NULL, Actions.Created, Actions.Modified) >= '$dateFrom' ";
        }
        if (isset($to)) {
            $dateTo = date("Y-m-d", Utilities::getTimestamp($to));
            $where = "$where AND IF(Actions.Modified IS NULL, Actions.Created, Actions.Modified) <= '$dateTo' ";
        }
        
        if (isset($creator)) {
            $username = mysql_real_escape_string($creator);
            $where = "$where AND Actions.User='$username' ";
        }
        
        if (isset($tags) && strlen($tags) > 0) {
            $tags = mysql_real_escape_string(trim($tags));
            $tagArray = explode(" ", $tags);
            $tagArray = array_unique($tagArray);
            $size = count($tagArray);
            $i = 1;
            $where = "$where AND (";
            foreach($tagArray as $tag) {
                $where = "$where Tags.Name='$tag' ";
                if ($i != $size) {
                    $where = "$where OR ";
                }
                $i++;
            }
            $where = "$where) ";
        }       
        
        if (isset($page) && isset($pageSize)) {
            $page = (int) $page;
            $pageSize = (int) $pageSize;
            $startPoint = ($page-1)* $pageSize;
        }
        else {
            $page = 1;
            $pageSize = 10;
            $startPoint = 0;
        }
       
        $sortByMap = array("date"=> "Actions.Created DESC",
                           "popularity" => "Actions.Popularity DESC");        
        if (isset($sortBy) && array_key_exists($sortBy, $sortByMap)) {                       
            $query = "$query $where GROUP BY Actions.ActionId ORDER BY {$sortByMap[$sortBy]} LIMIT $startPoint, $pageSize";
        }
        else {
            $query = "$query $where GROUP BY Actions.ActionId LIMIT $startPoint, $pageSize";   
        }        
        $countQuery = "$countQuery $where";
        
        //print "$query <br/><br/><br/>";
        //print "$countQuery <br/><br/><br/>";
        
        $actionPage = new ActionsPage();
        $actionPage->setTotal(0);
        $actionPage->setPageIndex($page);
        $actionPage->setPageSize($pageSize);
        
        $result = mysql_query($query);
        $resultCount = mysql_query($countQuery);
           
        if ($result  && mysql_num_rows($result)) {
            
            $countRow = mysql_fetch_assoc($resultCount);
            if ($countRow) {
                $actionPage->setTotal($countRow["Count"]);  
            }
            else {
                $actionPage->setTotal(0);
            }
            
            while ($row = mysql_fetch_assoc($result)) {
                $action = ActionsFactory::createAction($row["Type"]);
                $action->setId($row["ActionId"]);
                $action->setName($row["Name"]);
                $action->setDescription($row["Description"]);
                $action->setDateCreated(Utilities::getTimestampFromMySqlDatetime($row["Created"]));
                if (strlen($row["Modified"]) > 0 ) {
                    $action->setDateModified(Utilities::getTimestampFromMySqlDatetime($row["Modified"]));
                }
                $action->setPopularity($row["Popularity"]);
                $action->setVisits($row["Visits"]);
                $action->setTags($row["Tags"]);
                $action->setUser($row["Username"]);                
                if ($row["SeriesId"] != NULL) {
                    $action->setSeriesId($row["SeriesId"]);
                }
                else {
                    $action->setSeriesId(NULL);
                }                
                $actionPage->addAction($action);
            }
        }          
        
        return $actionPage;
    }
    
    public function getPetition($id) {
        $id = (int) $id;
        $petition = new Petition();
        if ($this->getAction($id, $petition)) {
            $result2 = mysql_query("SELECT Start, Stop, RequiresLogIn,
                                   (SELECT COUNT(*) FROM PetitionSignatures WHERE ActionId=$id) Signatures
                                   FROM Petitions WHERE ActionId=$id");
            if ($result2  && mysql_num_rows($result2)) {
                $row2 = mysql_fetch_assoc($result2);
                $petition->setStartDate(Utilities::getTimestampFromMySqlDate($row2["Start"]));
                $petition->setStopDate(Utilities::getTimestampFromMySqlDate($row2["Stop"]));
                $petition->setRequiresLogIn($row2["RequiresLogIn"]);
                $petition->setSignatures($row2["Signatures"]);
                return $petition;
            }
        }
        return NULL;
    }
    
    public function isPetitionSignedByUser($petitionId, $user) {
        $petitionId = (int) $petitionId;
        $user = mysql_real_escape_string($user);
        
        mysql_query("INSERT INTO Visits VALUES($petitionId, '$user', NOW());");
        
        $result = mysql_query("SELECT Comment FROM PetitionSignatures WHERE User='$user' AND ActionId=$petitionId");
        if ($result  && mysql_num_rows($result)) {
            return true;
        }
        return false;
    }
    
    public function signPetition($petitionId, $user, $comment) {
        $petitionId = (int) $petitionId;
        $user = mysql_real_escape_string($user);
        $comment = mysql_real_escape_string($comment);
        $query = "INSERT INTO PetitionSignatures VALUES($petitionId, '$user', '$comment')";
        return mysql_query($query);
    }
    
    public function getSurvey($id) {
        $id = (int) $id;
        $survey = new Survey();
        if ($this->getAction($id, $survey)) {
            $result2 = mysql_query("SELECT Start, Stop, RequiresLogIn FROM Surveys WHERE ActionId=$id");
            if ($result2  && mysql_num_rows($result2)) {
                $row2 = mysql_fetch_assoc($result2);
                $survey->setStartDate(Utilities::getTimestampFromMySqlDate($row2["Start"]));
                $survey->setStopDate(Utilities::getTimestampFromMySqlDate($row2["Stop"]));
                $survey->setRequiresLogIn($row2["RequiresLogIn"]);
                return $survey;
            }
        }
        return NULL;
    }    
    
    public function getDeliberation($id) {
        $id = (int) $id;
        $deliberation = new Deliberation();
        if ($this->getAction($id, $deliberation)) {
            return $deliberation;
        }
        return NULL;
    }       
        
    private function getAction($id, $action) {
        $id = (int) $id;
        $result = mysql_query("SELECT ActionId, User, Name, Description, Created, Modified,
                              Popularity, (SELECT COUNT(*) Visits FROM Visits WHERE ActionId=$id) Visits,
                              SeriesId
                              FROM Actions WHERE ActionId=$id");
        if ($result  && mysql_num_rows($result)) {           
            $row = mysql_fetch_assoc($result);
            $action->setId($id);
            $action->setUser($row["User"]);
            $action->setName($row["Name"]);
            $action->setDescription($row["Description"]);
            $action->setDateCreated(Utilities::getTimestampFromMySqlDatetime($row["Created"]));
            if (strlen($row["Modified"]) > 0 ) {
                $action->setDateModified(Utilities::getTimestampFromMySqlDatetime($row["Modified"]));
            }
            $action->setPopularity($row["Popularity"]);
            $action->setVisits($row["Visits"]);
            
            if ($row["SeriesId"] != NULL) {
                $action->setSeriesId($row["SeriesId"]);
            }
            else {
                $action->setSeriesId(NULL);
            }
            
            $result2 = mysql_query(
                "SELECT GROUP_CONCAT(Tags.Name SEPARATOR ' ') AS Tags FROM Tags
                    INNER JOIN ActionTags ON ActionTags.TagId = Tags.TagId
                    INNER JOIN Actions ON Actions.ActionId = ActionTags.ActionId WHERE Actions.ActionId=$id");
            if ($result2  && mysql_num_rows($result2)) {
                $row2 = mysql_fetch_assoc($result2);
                $action->setTags($row2["Tags"]);
            }
            return true;
        }
        return false;        
    }
    
    public function updateVisits() {
        $result = mysql_query("SELECT ActionId FROM Actions");
        if ($result  && mysql_num_rows($result)) {           
            while ($row = mysql_fetch_assoc($result)) {
                $id=$row["ActionId"];
                $query = "UPDATE Actions SET Visits=(SELECT COUNT(*) FROM Visits WHERE ActionId=$id)
                          WHERE ActionId=$id";
                mysql_query($query);
                $query = "UPDATE Actions SET Popularity=Visits WHERE ActionId=$id";
                mysql_query($query);                
            }
        }
    }
    
    public function saveComment($actionId, $user, $comment) {
        $actionId = (int) $actionId;
        $user = mysql_real_escape_string($user);
        $comment = mysql_real_escape_string($comment);
        $query = "INSERT INTO Comments	(`ActionId`, `User`, `Comment`, `Time`)
                            VALUES ($actionId, '$user', '$comment', CURRENT_TIMESTAMP())";
        if (!mysql_query($query)) {
            return -1;
        }
        return mysql_insert_id();
    }
    
    public function getComments($actionId, $pageSize, $page) {
        $actionId = (int) $actionId;       
        $page = (int) $page;
        $pageSize = (int) $pageSize;
        $startPoint = ($page-1)* $pageSize;        
        
        $query = "SELECT User, Comment, Time FROM Comments WHERE ActionId=$actionId
                  ORDER BY Time DESC LIMIT $startPoint, $pageSize";
        $countQuery = "SELECT COUNT(ActionId) as Count FROM Comments WHERE ActionId=$actionId";
  
        $commentsPage = new CommentsPage();
        $commentsPage->setTotal(0);
        $commentsPage->setPageIndex($page);
        $commentsPage->setPageSize($pageSize);
        
        $result = mysql_query($query);
        $resultCount = mysql_query($countQuery);
           
        if ($result  && mysql_num_rows($result)) {
            $countRow = mysql_fetch_assoc($resultCount);
            if ($countRow) {
                $commentsPage->setTotal($countRow["Count"]);
            }
            
            while ($row = mysql_fetch_assoc($result)) {
                $comment = new Comment();
                $comment->setUser($row["User"]);
                $comment->setComment($row["Comment"]);
                $comment->setTime(Utilities::getTimestampFromMySqlDatetime($row["Time"]));                
                $commentsPage->addComment($comment);
            }
        }
        return $commentsPage;
    }
}
?>